package Colecciones;

import Modelo.EjecutaConsulta;
import Modelo.Fila;
import Modelo.TransaccionSql;
import java.io.PrintStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

public class Empleado
{
  public static Vector getUsuarios(String p_emplid, int p_reg)
  {
    Vector datos = new Vector();
    try {
      String strTablas = " FROM PSOPRDEFN DE, SYSADM.PS_PERSONAL_DATA DT";

      String consulta = " SELECT  DT.EMPLID CONSECUTIVO,  TRIM(DT.FIRST_NAME) NOMBRES,  TRIM(DT.LAST_NAME) APELLIDOS,  TRIM(DE.OPRID) NOMBRE_USUARIO,  TRIM(DE.OPERPSWD) PASSWORD,  TRIM(DE.EMAILID) CORREO" + strTablas + " WHERE" + "  DE.EMPLID = DT.EMPLID" + "  AND DE.EMPLID LIKE '%" + p_emplid + "%'";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + " ORDER BY DT.FIRST_NAME, DT.LAST_NAME";
      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Consultas.getUsuarios " + e.toString());
    }
    return datos;
  }

  public static Fila getUsuario(String p_emplid)
  {
    Fila dato = new Fila();
    try {
      String strTablas = " FROM PSOPRDEFN DE, SYSADM.PS_PERSONAL_DATA DT";

      String consulta = " SELECT  DT.EMPLID CONSECUTIVO,  TRIM(DT.FIRST_NAME) NOMBRES,  TRIM(DT.LAST_NAME) APELLIDOS,  TRIM(DE.OPRID) NOMBRE_USUARIO,  TRIM(DE.OPERPSWD) PASSWORD,  TRIM(DE.EMAILID) CORREO" + strTablas + " WHERE" + "  DE.EMPLID = DT.EMPLID" + "  AND DE.EMPLID = " + p_emplid;

      dato = EjecutaConsulta.Ejecutar2(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Consultas.getUsuarios " + e.toString());
    }
    return dato;
  }

  public static Vector getCargosPorPersona(String p_emplid, int p_reg)
  {
    Vector datos = new Vector();
    try {
      String consulta = " SELECT   PE.EMPLID,  PE.EMPLOYER,  PE.CITY,  PE.ENDING_TITLE,  PE.EXT_ORG_ID EMPRESA,  PE.US_SOC_CD  CONS_CARGO,  CAR.DESCR DESCRIPCION_CARGO FROM  PS_PRIORWORK_EXPER PE,  PS_US_SOC_TBL CAR WHERE  PE.US_SOC_CD = CAR.US_SOC_CD  AND PE.EMPLID = '" + p_emplid + "' ";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Consultas.getCargosPorPersona " + e.toString());
    }
    return datos;
  }

  public static int getCurrentEmplId()
    throws SQLException
  {
    String consulta = " SELECT  EMPLID_LAST_CS  FROM  PS_INSTALLATION_SA";

    ResultSet rs = TransaccionSql.ejecutarConsulta(consulta);
    int emplid = 0;
    if (rs != null) {
      rs.next();
      emplid = rs.getInt("EMPLID_LAST_CS");
      rs.close();
    } else {
      Utils.write("el resulset viene nulo!!!");
    }
    Utils.write("ultimo EMPLID: " + emplid);
    return emplid;
  }

  public static String getNewEmplId()
    throws SQLException
  {
    int emplid = getCurrentEmplId() + 1;
    String consulta = " UPDATE   PS_INSTALLATION_SA SET   EMPLID_LAST_CS = " + emplid;

    int result = TransaccionSql.actualizar(consulta);

    return String.valueOf(emplid);
  }

  public static Vector buscarPersona(Fila criterio)
  {
    Vector datos = new Vector();
    try {
      String strTablas = " FROM SYSADM.PS_PERSONAL_DATA DT";

      String consulta = " SELECT   DT.EMPLID,   TRIM(DT.FIRST_NAME) NOMBRES,   TRIM(DT.LAST_NAME) APELLIDOS,   PN.NATIONAL_ID_TYPE TIPO_DOCUMENTO,   PN.NATIONAL_ID NUMERO_DOCUMENTO,   DT.CITY CIUDAD_RESIDENCIA,   DT.SEX GENERO,   DT.MAR_STATUS ESTADO_CIVIL,   DT.BIRTHDATE FECHA_NACIMIENTO,   DT.BIRTHCOUNTRY PAIS_NACIMIENTO,   DT.BIRTHPLACE CIUDAD_NACIMIENTO,   DT.CITY CIUDAD_RESIDENCIA,   DT.PHONE TELEFONO_RESIDENCIA,   DT.ADDRESS1 DIRECCION_RESIDENCIA,   PM.EMAIL_ADDR CORREO" + strTablas + "   LEFT JOIN PS_PERS_NID PN ON PN.EMPLID = DT.EMPLID" + "   LEFT JOIN PS_EMAIL_ADDRESSES PM ON PM.EMPLID = DT.EMPLID" + " WHERE" + "   PM.PREF_EMAIL_FLAG = 'Y'";

      String documento = Utils.getValor(criterio, "NUMERO_DOCUMENTO");

      String emplid = Utils.getValor(criterio, "EMPLID");

      Utils.write("NUMERO_DOCUMENTO : " + documento);
      Utils.write("EMPLID :" + emplid);
      if ((documento != null) && (documento.length() > 0)) {
        consulta = consulta + "   AND PN.NATIONAL_ID = '" + documento + "'";
        Utils.write("A:PN.NATIONAL_ID SI");
      } else {
        Utils.write("A:PN.NATIONAL_ID NO");
      }

      if ((emplid != null) && (emplid.length() > 0)) {
        consulta = consulta + "   AND DT.EMPLID = '" + emplid + "'";
        Utils.write("A:DT.EMPLID SI");
      } else {
        Utils.write("A:DT.EMPLID NO");
      }

      consulta = consulta + " ORDER BY DT.FIRST_NAME, DT.LAST_NAME";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empleado.buscarPersona " + e.toString());
    }
    return datos;
  }

  public static boolean existePersona(String nro_doc)
  {
    boolean result = false;
    try {
      String consulta = " SELECT    * FROM   PS_PERS_NID WHERE   NATIONAL_ID = '" + nro_doc + "'";

      ResultSet rs = TransaccionSql.ejecutarConsulta(consulta);
      try {
        rs.first();
        result = true;
      } catch (SQLException e) {
      }
      finally {
        rs.close();
      }
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empleado.existePersona " + e.toString());
    }
    return result;
  }

  /**
   * @deprecated
   */
  public static Vector listadoEstudiantesPregrado(int p_reg)
  {
    Vector datos = new Vector();
    try {
      String strTablas = " FROM matfc.estudiantes e, SYSADM.PS_PERSONAL_DATA B, SYSADM.PS_ACAD_PROG_TBL car";

      String consulta = " SELECT   e.emplid,   TRIM(b.first_name) nombres,   TRIM(b.last_name) apellidos,   prg_codigo,   TRIM(descr) Carrera,   semestre_actual(e.emplid, e.acad_career, 'PUJCL',stdnt_car_nbr, prg_codigo,' ') Semestre,   decode(e.acad_career,'UGRD','Pregrado','GRAD','Posgrado') Nivel" + strTablas + " WHERE" + "   e.prg_codigo = car.acad_prog " + "   AND e.emplid = b.emplid " + "   AND eas_codigo = 'AC' " + "   AND e.acad_career = 'UGRD'";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + " ORDER BY TRIM(B.FIRST_NAME), TRIM(B.LAST_NAME)";
      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      e.printStackTrace();
      System.out.println("Error: en la consulta de Empleado.listadoEstudiantesPregrado " + e.toString());
    }
    return datos;
  }

  /**
   * @deprecated
   */
  public static Vector listadoEstudiantesPorSemestre(int semestre, int p_reg)
  {
    Vector datos = new Vector();

    if ((semestre < 1) || (semestre > 11))
      return datos;

    try
    {
      String strTablas = " FROM matfc.estudiantes e, SYSADM.PS_PERSONAL_DATA B, SYSADM.PS_ACAD_PROG_TBL car";

      String consulta = " SELECT   e.emplid,   TRIM(b.first_name) nombres,   TRIM(b.last_name) apellidos,   prg_codigo,   TRIM(descr) Carrera,   semestre_actual(      e.emplid,       e.acad_career,       'PUJCL',      stdnt_car_nbr,       prg_codigo,      ' ') Semestre,   decode(e.acad_career,'UGRD','Pregrado','GRAD','Posgrado') Nivel" + strTablas + " WHERE" + "   e.prg_codigo = car.acad_prog" + "   AND e.emplid = b.emplid" + "   AND eas_codigo = 'AC'" + "   AND  semestre_actual(e.emplid, e.acad_career, 'PUJCL'," + "           stdnt_car_nbr, prg_codigo,' ') = ";

      consulta = consulta + String.valueOf(semestre);
      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + " ORDER BY TRIM(B.FIRST_NAME), TRIM(B.LAST_NAME)";
      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      e.printStackTrace();
      System.out.println("Error: en la consulta de Empleado.listadoEstudiantesPorSemestre " + e.toString());
    }
    return datos;
  }

  /**
   * @deprecated
   */
  public static Vector listadoEstudiantesPostgrado(int p_reg)
  {
    Vector datos = new Vector();
    try {
      String strTablas = " FROM matfc.estudiantes e, SYSADM.PS_PERSONAL_DATA B, SYSADM.PS_ACAD_PROG_TBL car";

      String consulta = " SELECT   e.emplid,   TRIM(b.first_name) nombres,   TRIM(b.last_name) apellidos,   prg_codigo,   TRIM(descr) Carrera,   semestre_actual(e.emplid, e.acad_career, 'PUJCL',stdnt_car_nbr, prg_codigo,' ') Semestre,   decode(e.acad_career,'UGRD','Pregrado','GRAD','Posgrado') Nivel" + strTablas + " WHERE" + "   e.prg_codigo = car.acad_prog " + "   AND e.emplid = b.emplid " + "   AND eas_codigo = 'AC' " + "   AND e.acad_career = 'GRAD'";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + " ORDER BY TRIM(B.FIRST_NAME), TRIM(B.LAST_NAME)";
      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      e.printStackTrace();
      System.out.println("Error: en la consulta de Empleado.listadoEstudiantesPostgrado " + e.toString());
    }
    return datos;
  }

  /**
   * @deprecated
   */
  public static Vector anoGrado(int emplid)
  {
    Vector datos = new Vector();
    try {
      String strTablas = " FROM SYSADM.PS_ACAD_DEGR A, SYSADM.PS_DEGREE_TBL B";

      String consulta = " SELECT   a.emplid,   b.descr Titulo,   to_number(to_char(a.degr_status_date,'YYYY')) Anno_grado,   decode(acad_career,'UGRD','Pregrado','GRAD','Posgrado') Nivel,   a.institution" + strTablas + " WHERE" + "   a.degree = b.degree" + "   AND a.emplid = '" + String.valueOf(emplid) + "' ";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de Empleado.anoGrado " + e.toString());
    }
    return datos;
  }

  /**
   * @deprecated
   */
  public static Vector listadoAnoGradoPosgrado()
  {
    Vector datos = new Vector();
    try {
      String strTablas = " FROM SYSADM.PS_ACAD_DEGR A, SYSADM.PS_DEGREE_TBL B, matfc.estudiantes e";

      String consulta = " SELECT   to_number(to_char(a.degr_status_date,'YYYY')) Anno_grado   decode(acad_career,'UGRD','Pregrado','GRAD','Posgrado') Nivel" + strTablas + " WHERE" + "   a.degree = b.degree" + "   AND e.acad_career = 'GRAD'" + " GROUP BY to_number(to_char(a.degr_status_date,'YYYY'))" + " ORDER BY to_number(to_char(a.degr_status_date,'YYYY'))";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      e.printStackTrace();
      System.out.println("Error: en la consulta de Empleado.anoGrado " + e.toString());
    }
    return datos;
  }

  /**
   * @deprecated
   */
  public static Vector listadoAnoGradoPregrado()
  {
    Vector datos = new Vector();
    try {
      String strTablas = " FROM matfc.estudiantes e, SYSADM.PS_ACAD_PROG_TBL car, SYSADM.PS_ACAD_DEGR egre";

      String consulta = "SELECT e.emplid, prg_codigo, descr CARRERA, to_number(to_char(egre.degr_status_date,'YYYY')) Ano_grado, decode(e.acad_career,'UGRD','Pregrado','GRAD','Posgrado') NIVEL" + strTablas + " WHERE e.prg_codigo = car.acad_prog AND e.emplid = egre.emplid" + " AND  causa_estado(e.emplid,e.acad_career,e.stdnt_car_nbr," + " e.prg_codigo,'PUJCL') = 'GR'";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      e.printStackTrace();
      System.out.println("Error: en la consulta de Empleado.anoGrado " + e.toString());
    }
    return datos;
  }

  /**
   * @deprecated
   */
  public static Vector listadoEgresados(int p_reg)
  {
    Vector datos = new Vector();
    try {
      String strTablas = " FROM SYSADM.PS_ACAD_DEGR a, SYSADM.PS_PERSONAL_DATA B";

      String consulta = " SELECT   a.emplid,   TRIM(b.first_name) nombres,   TRIM(b.last_name) apellidos,   decode(acad_career,'UGRD','Pregrado','GRAD','Posgrado') Nivel" + strTablas + " WHERE" + "   a.emplid = b.emplid ";

      if (p_reg > 0)
        consulta = consulta + " AND ROWNUM <= " + String.valueOf(p_reg);

      consulta = consulta + " ORDER BY TRIM(B.FIRST_NAME), TRIM(B.LAST_NAME)";
      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      e.printStackTrace();
      System.out.println("Error: en la consulta de Empleado.listadoEgresados " + e.toString());
    }
    return datos;
  }
}